/*
 * EmployeeDaoJdbcImpl.java
 *
 * Project: TECHWATCH - TESTING TECHNOLOGIES
 *
 * Copyright 2008 by ELCA Informatique SA
 * Av. de la Harpe 22-24, 1000 Lausanne 13
 * All rights reserved.
 *
 * This software is the confidential and proprietary information
 * of ELCA Informatique SA. ("Confidential Information"). You
 * shall not disclose such Confidential Information and shall
 * use it only in accordance with the terms of the license
 * agreement you entered into with ELCA.
 */

package ch.elca.training.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import ch.elca.training.dao.EmployeeDao;
import ch.elca.training.dao.generic.GenericDaoJdbcImpl;
import ch.elca.training.dom.Employee;

@Repository
public class EmployeeDaoJdbcImpl extends GenericDaoJdbcImpl<Employee> implements
		EmployeeDao {
	private static final String SQL_GET_ALL_IN_THE_LIST_GROUP = "select * from employee where group_id in (:idList)";
	private static final String SQL_GET_ALL_IN_THE_SAME_GROUP = "select * from employee where group_id = ?";
	private static final String SQL_GET_ALL = "select * from employee";
	private static final String SQL_READ = "select * from employee where id = ?";
	private static final String SQL_CREATE = "insert into employee (id,visa, leaderId, groupId) values (?, ?, ?,?)";
	private static final String SQL_UPDATE = "update employee set visa = ?, leaderId = ?, groupId = ? where id = ?";
	private static final String SQL_DELETE = "delete from employee where id = ?";

	@Override
	public void create(Employee newInstance) {
		// TODO: [BASIC] Implements this method to insert new Employee into
		// database.
		getJdbcTemplate().update(SQL_CREATE, getNextSequenceValue(),
				newInstance.getVisa(), newInstance.getLeaderId(),
				newInstance.getGroupId());

	}

	@Override
	public Employee read(Long id) {
		// TODO: [BASIC] Implements this method to read Employee from database.
		return getJdbcTemplate().queryForObject(SQL_READ, new Object[] { id },
				new RowMapper<Employee>() {

					@Override
					public Employee mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Employee employee = new Employee();
						employee.setVisa(rs.getString("VISA"));
						employee.setLeaderId(rs.getLong("RANK_ID"));
						employee.setGroupId(rs.getLong("GROUP_ID"));
						return employee;
					}

				});
	}

	@Override
	public void update(Employee transientObject) {
		// TODO: [BASIC] Implements this method to update an existing Employee
		// in database.
		getJdbcTemplate().update(SQL_UPDATE, transientObject.getVisa(),
				transientObject.getLeaderId(), transientObject.getGroupId(),
				transientObject.getId());

	}

	@Override
	public void delete(Employee persistedObject) {
		// TODO: [BASIC] Implements this method to delete an existing Employee
		// from database.
		getJdbcTemplate().update(SQL_DELETE, persistedObject.getId());

	}

	@Override
	public List<Employee> getAll() {
		return getJdbcTemplate().query(SQL_GET_ALL, new RowMapper<Employee>() {
			@Override
			public Employee mapRow(ResultSet rs, int rowNum)
					throws SQLException {
				Employee employee = new Employee();
				employee.setVisa(rs.getString("VISA"));
				employee.setLeaderId(rs.getLong("RANK_ID"));
				employee.setGroupId(rs.getLong("GROUP_ID"));
				return employee;
			}

		});
	}

	public List<Employee> getAllEmployeeInTheSameGroup(Long groupId) {
		return getJdbcTemplate().query(SQL_GET_ALL_IN_THE_SAME_GROUP,
				new Object[] { groupId }, new RowMapper<Employee>() {
					@Override
					public Employee mapRow(ResultSet rs, int rowNum)
							throws SQLException {
						Employee employee = new Employee();
						employee.setVisa(rs.getString("VISA"));
						employee.setLeaderId(rs.getLong("RANK_ID"));
						employee.setGroupId(rs.getLong("GROUP_ID"));
						return employee;
					}

				});
	}

	@Override
	public List<Employee> getAllEmployeeInTheListGroup(List<String> groupId) {
		NamedParameterJdbcTemplate nameJdbcTemplate = new NamedParameterJdbcTemplate(
				getDataSource());
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("idList", groupId);
		return nameJdbcTemplate.query(SQL_GET_ALL_IN_THE_LIST_GROUP, params,
				ParameterizedBeanPropertyRowMapper.newInstance(Employee.class));
	}
}
